package com.blog.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Vector;

import com.blog.dto.Replay;
import com.blog.util.DataAccess;
import com.blog.util.DateTime;

public class ReplayDao {
	/**
	 * 添加评论
	 * @param rep
	 * @return
	 */
	public boolean addReplay(Replay rep) {
		boolean flag = false ;
		Connection con = DataAccess.getInstance().getConnection();
		PreparedStatement ps = null ;
		String sql = "insert into  replay  values(null,?,?,?,?,null)";
		try {
			ps = con.prepareStatement(sql);
			ps.setString(1, rep.getUsername());
			ps.setString(2, rep.getContent());
			ps.setTimestamp(3, rep.getPublish_time());
			ps.setInt(4, rep.getArticle_id());
			if(ps.executeUpdate() == 1) flag=true;
		} catch (SQLException e) {
			System.err.println("sql语句执行异常!插入失败");
			e.printStackTrace();
		}finally {
			DataAccess.getInstance().closeConnection( con , ps);
		}
		return flag;
	}
	/**
	 * 修改评论，主要用于过滤
	 * @param rep
	 * @return
	 */
	public boolean editReplay(Replay rep) {
		boolean flag = false ;
		Connection con = DataAccess.getInstance().getConnection();
		PreparedStatement ps = null ;
		String sql = "	update replay set content = ?  where  replay_id = ? AND delete_at is NULL " ;
		try {
			ps = con.prepareStatement(sql);
			ps.setString(1, rep.getContent());
			ps.setInt(2, rep.getReplay_id());
			if(ps.executeUpdate() == 1) flag=true;
		} catch (SQLException e) {
			System.err.println("sql语句执行异常!更新失败");
			e.printStackTrace();
		}finally {
			DataAccess.getInstance().closeConnection( con , ps);
		}
		return flag;
	}
	
	/**
	 * 根据评论Id删除评论
	 * @param _id
	 * @return
	 */
	public boolean delReplayById(int _id) {
		boolean flag = false ;
		Connection con = DataAccess.getInstance().getConnection();
		PreparedStatement ps = null ;
		String sql = "	update replay set delete_at = ?  where  replay_id = ? AND delete_at is NULL " ;
		try {
			ps = con.prepareStatement(sql);
			ps.setTimestamp(1, DateTime.getDateTime());
			ps.setInt(2, _id);
			if(ps.executeUpdate() == 1) flag=true;
		} catch (SQLException e) {
			System.err.println("sql语句执行异常!删除失败");
			e.printStackTrace();
		}finally {
			DataAccess.getInstance().closeConnection( con , ps);
		}
		return flag;
	}
	/**
	 * 查找所有评论
	 * @return
	 */
	public Vector<Replay> findAllReplay() {
		Vector<Replay> v = new Vector<Replay>();
		Connection con = DataAccess.getInstance().getConnection();
		PreparedStatement ps = null ;
		ResultSet rs = null;
		String sql = " select * from replay where delete_at  is  NULL  ";
		try {
			ps = con.prepareStatement(sql);
			rs = ps.executeQuery();
			while (rs.next()) {
				Replay replay = new Replay();
				replay.setArticle_id(rs.getInt("article_id"));
				replay.setContent(rs.getString("content"));
				replay.setPublish_time(rs.getTimestamp("publish_time"));
				replay.setReplay_id(rs.getInt("replay_id"));
				replay.setUsername(rs.getString("username"));
				v.add(replay);
			}
		} catch (SQLException e) {
			System.err.println("sql语句执行异常!");
			e.printStackTrace();
		}finally {
			DataAccess.getInstance().closeConnection(rs, ps, con);
		}
		return v;
	}
	
	/**
	 * 根据用户名查找用户评论
	 * @return
	 */
	public Vector<Replay> findReplayByUsername(String username) {
		Vector<Replay> v = new Vector<Replay>();
		Connection con = DataAccess.getInstance().getConnection();
		PreparedStatement ps = null ;
		ResultSet rs = null;
		String sql = " select * from replay where username = ? AND delete_at  is  NULL  ";
		try {
			ps = con.prepareStatement(sql);
			ps.setString(1, username);
			rs = ps.executeQuery();
			while (rs.next()) {
				Replay replay = new Replay();
				replay.setArticle_id(rs.getInt("article_id"));
				replay.setContent(rs.getString("content"));
				replay.setPublish_time(rs.getTimestamp("publish_time"));
				replay.setReplay_id(rs.getInt("replay_id"));
				replay.setUsername(rs.getString("username"));
				v.add(replay);
			}
		} catch (SQLException e) {
			System.err.println("sql语句执行异常!");
			e.printStackTrace();
		}finally {
			DataAccess.getInstance().closeConnection(rs, ps, con);
		}
		return v;
	}
	
	/**
	 * 根据文章ID查找评论
	 * @param art_id
	 * @return
	 */
	public Vector<Replay> findReplayByArticle_id(int art_id) {
		Vector<Replay> v = new Vector<Replay>();
		Connection con = DataAccess.getInstance().getConnection();
		PreparedStatement ps = null ;
		ResultSet rs = null;
		String sql = " select * from replay where article_id = ? AND delete_at  is  NULL  ";
		try {
			ps = con.prepareStatement(sql);
			ps.setInt(1, art_id);
			rs = ps.executeQuery();
			while (rs.next()) {
				Replay replay = new Replay();
				replay.setArticle_id(rs.getInt("article_id"));
				replay.setContent(rs.getString("content"));
				replay.setPublish_time(rs.getTimestamp("publish_time"));
				replay.setReplay_id(rs.getInt("replay_id"));
				replay.setUsername(rs.getString("username"));
				v.add(replay);
			}
		} catch (SQLException e) {
			System.err.println("sql语句执行异常!");
			e.printStackTrace();
		}finally {
			DataAccess.getInstance().closeConnection(rs, ps, con);
		}
		return v;
	}
	
	/**
	 * 根据评论内容查找评论
	 * @param _content
	 * @return
	 */
	public Vector<Replay> findReplayByContent(String _content) {
		Vector<Replay> v = new Vector<Replay>();
		Connection con = DataAccess.getInstance().getConnection();
		PreparedStatement ps = null ;
		ResultSet rs = null;
		String sql = " select * from replay where content LIKE ? AND delete_at  is  NULL  ";
		try {
			ps = con.prepareStatement(sql);
			ps.setString(1, "%"+_content+"%");
			rs = ps.executeQuery();
			while (rs.next()) {
				Replay replay = new Replay();
				replay.setArticle_id(rs.getInt("article_id"));
				replay.setContent(rs.getString("content"));
				replay.setPublish_time(rs.getTimestamp("publish_time"));
				replay.setReplay_id(rs.getInt("replay_id"));
				replay.setUsername(rs.getString("username"));
				v.add(replay);
			}
		} catch (SQLException e) {
			System.err.println("sql语句执行异常!");
			e.printStackTrace();
		}finally {
			DataAccess.getInstance().closeConnection(rs, ps, con);
		}
		return v;
	}
	
}
